	import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
	
public class tableQuery {
		
		public static void main(String[] args) throws Exception{


			Connection con = null;
			try{
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				con = DriverManager.getConnection("jdbc:sqlserver://10.247.252.111:1433","sa","Welcome2LIG");
				System.out.println("First connection ok.");
				
				Statement stmtTB = con.createStatement();
				Statement stmtTBCon = con.createStatement();
				Statement stmtCol = con.createStatement();
				Statement stmtColCon = con.createStatement();
				Statement stmtTbCol = con.createStatement();
				ResultSet rsTb;
				ResultSet rsTbCon;
				ResultSet rsCol;
				ResultSet rsColCon;
				ResultSet rsTbCol;
				
				
				PrintStream out = new PrintStream(new FileOutputStream("D:/OutFile3.csv"));	
				
				out.println("TABLE_NAME|COLUMN_NAME|COUNTS");
				
				//get table name
				String sqlTB = "SELECT distinct TABLE_NAME " +
								"FROM COPY_OF_CDB.INFORMATION_SCHEMA.COLUMNS " +
								"WHERE TABLE_NAME <> 'ARCHIVE_CITYFILE_KEY_CONVERSION' " +
										"AND TABLE_NAME NOT LIKE 'View%' " +
								"order by TABLE_NAME";
				
				String sqlTBCon = "Select count(*) counts " +
						"From (SELECT distinct TABLE_NAME " +
								"FROM COPY_OF_CDB.INFORMATION_SCHEMA.COLUMNS)a" ;
				
				rsTb = stmtTB.executeQuery(sqlTB);
				rsTbCon = stmtTBCon.executeQuery(sqlTBCon);
				
				rsTbCon.next();
				int tbCon = rsTbCon.getInt(1);
				System.out.println(tbCon);

				//set table array
				String[] tb = new String[tbCon];
				
				int t=0;
				while(rsTb.next()){
					tb[t] = rsTb.getString(1);
					System.out.println(tb[t]+" table is processing");
					
					String ColName = "SELECT distinct COLUMN_NAME " +
							"FROM COPY_OF_CDB.INFORMATION_SCHEMA.COLUMNS " +
							"WHERE TABLE_NAME = '"+tb[t]+"'";
					
					String ColNameCount = "SELECT COUNT(*) ColCounts "+
										"FROM("+
											"SELECT distinct COLUMN_NAME " +
											"FROM COPY_OF_CDB.INFORMATION_SCHEMA.COLUMNS " +
											"WHERE TABLE_NAME = '"+tb[t]+"'" +
											")B";
					
					rsCol = stmtCol.executeQuery(ColName);
					rsColCon = stmtColCon.executeQuery(ColNameCount);
					
					rsColCon.next();
					int colCon = rsColCon.getInt(1);
					
					String[] col = new String[colCon];
					//System.out.println(colCon);
					int c=0;
					while(rsCol.next()){
						col[c] = rsCol.getString(1);
						//System.out.println(col[c]);
						String TbCol = "SELECT '"+tb[t]+"' TABLE_NAME, '"+col[c]+"' COLUMN_NAME, COUNT("+col[c]+") COUNTS "+
										"FROM COPY_OF_CDB.dbo."+tb[t];
						
						rsTbCol = stmtTbCol.executeQuery(TbCol);
						
						rsTbCol.next();
						String tableName = rsTbCol.getString(1);
						String columnName = rsTbCol.getString(2);
						int counts = rsTbCol.getInt(3);
						
						out.println(tableName+"|"+columnName+"|"+counts);
						
						c+=1;
						
					}
					
					
					
					
					//out.println(tb[t]);
					
					t+=1;
				}
				
				//out.close();
				//stmt.close();
				//stmt2.close();
				//con.close();
				
			}
			catch (Exception e){
				System.err.println("Exception: " + e.getMessage());
				throw new Exception(e);
				
			}
				
			System.out.println("The Process is completed!!!!");			
		}
	
}
